An Investigation of the Efficiency of SQL DML Operations Performed on an ORACLE DBMS using SAS/ACCESS Software

In an international epidemiological study of 2000 cardiac surgery patients, the data of 7000 variables are entered through a Visual Basic data entry system and stored in 57 large ORACLE tables. A SAS application is developed to automatically convert the ORACLE tables to SAS data sets, perform a series of intensive data processing, and based upon the result of the data processing, dynamically pass ORACLE SQL Data Manipulation Language (DML) commands such as UPDATE, DELETE and INSERT to ORACLE database and modify the data in the 57 tables. The modification of ORACLE data using SAS software can be resource-intensive, especially in dealing with large tables and involving sorting in ORACLE data. To select an efficient method for the SAS application, this paper focuses on the investigation of the efficiency of these four methods provided by SAS/ACCESS software. 1. SQL Procedure Pass-Through Facility 2. DBLOAD Procedure SQL Statement 3. SQL Procedure with View Descriptor 4. DATA Step MODIFY Statement with View Descriptor The SQL procedure pass-through facility is discovered to be the most efficient one. The second method is acceptable. The performance of the last 2 methods is unsatisfactory, because they involve view descriptors and sorting in ORACLE data.